﻿Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.OleDb
Imports System.Configuration
Imports System.Data.OracleClient
Public Class clsDatabase

    Private objConn As OleDbConnection
    ' Private objConnOrcl As New System.Data.OracleClient.OracleConnection
    Private objCmd As OleDbCommand
    Private Trans As OleDbTransaction
    Private strConnString As String

    Sub New()

        'Dim oConfig As System.Configuration.Configuration

        'Dim a As String = 
        '' oConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/GHBankKYC")

        'If (oConfig.ConnectionStrings.ConnectionStrings.Count > 0) Then
        Try
            strConnString = ConfigurationManager.ConnectionStrings("GHBankAMLConnectionString").ConnectionString
            ' strConnString = "Provider=OraOLEDB.Oracle;Data Source=GHBKYC;User Id=KYCADMIN;Password=oracle;Unicode=True"
        Catch
            Throw New ApplicationException("Could not load the database")
        End Try
        ' Else

        'End If
    End Sub

    Public Function cn() As OleDbConnection

        objConn = New OleDbConnection
        If objConn.State = Data.ConnectionState.Closed Then
            objConn.ConnectionString = strConnString
        End If

        Return objConn
    End Function

    'Public Function cnOrcl() As OracleConnection

    '    objConnOrcl = New OracleConnection
    '    If objConn.State = Data.ConnectionState.Closed Then
    '        objConnOrcl.ConnectionString = strConnString
    '    End If

    '    Return objConnOrcl
    'End Function

    Public Function QueryDataReader(ByVal strSQL As String) As OleDbDataReader
        Dim dtReader As OleDbDataReader
        objConn = New OleDbConnection
        With objConn
            .ConnectionString = strConnString
            .Open()
        End With
        objCmd = New OleDbCommand(strSQL, objConn)
        dtReader = objCmd.ExecuteReader()
        Return dtReader '*** Return DataReader ***'
    End Function

    Public Function QueryDataSet(ByVal strSQL As String, ByVal strTableName As String) As DataSet
        Dim ds1 As New DataSet
        Dim dtAdapter As New OleDbDataAdapter
        objConn = New OleDbConnection
        With objConn
            .ConnectionString = strConnString
            .Open()
        End With
        objCmd = New OleDbCommand
        With objCmd
            .Connection = objConn
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        dtAdapter.SelectCommand = objCmd
        dtAdapter.Fill(ds1, strTableName)
        objConn.Close()
        Return ds1 '*** Return DataSet ***'
    End Function

    Public Function QueryDataSet(ByVal strSQL As String, ByVal clnDataColumn As Collection, ByVal strTableName As String) As DataSet
        Dim ds As New DataSet
        Dim dtAdapter As New OleDbDataAdapter

        objConn = New OleDbConnection
        With objConn
            .ConnectionString = strConnString
            .Open()
        End With

        objCmd = New OleDbCommand
        With objCmd
            .Connection = objConn
            .CommandText = strSQL
            .CommandType = CommandType.Text
        End With
        AddParameter(clnDataColumn)
        dtAdapter.SelectCommand = objCmd
        dtAdapter.Fill(ds, strTableName)
        dtAdapter.Dispose()


        Return ds   '*** Return DataSet ***' 
    End Function

    Private Sub AddParameter(ByVal clnDataColumn As Collection)
        Dim countCollection As Integer = 0
        countCollection = clnDataColumn.Count
        Dim objClsVariable As clsVariable = New clsVariable
        With objCmd

            For Each Data As Object In clnDataColumn
                objClsVariable = Data

                If objClsVariable.valueColumn Is Nothing Then
                    .Parameters.AddWithValue(":" + objClsVariable.nameColumn, DBNull.Value)
                    Continue For
                End If

                If ("string".Equals(objClsVariable.valueColumn.GetType.Name.ToLower)) Then
                    .Parameters.Add(":" + objClsVariable.nameColumn, OleDbType.VarWChar).Value = objClsVariable.valueColumn

                ElseIf ("integer".Equals(objClsVariable.valueColumn.GetType.Name.ToLower)) Then
                    .Parameters.Add(":" + objClsVariable.nameColumn, OleDbType.Integer).Value = objClsVariable.valueColumn

                ElseIf ("int32".Equals(objClsVariable.valueColumn.GetType.Name.ToLower)) Then
                    .Parameters.Add(":" + objClsVariable.nameColumn, OleDbType.Integer).Value = objClsVariable.valueColumn

                ElseIf ("double".Equals(objClsVariable.valueColumn.GetType.Name.ToLower)) Then
                    .Parameters.Add(":" + objClsVariable.nameColumn, OleDbType.Double).Value = objClsVariable.valueColumn

                ElseIf ("long".Equals(objClsVariable.valueColumn.GetType.Name.ToLower)) Then
                    .Parameters.Add(":" + objClsVariable.nameColumn, OleDbType.Double).Value = objClsVariable.valueColumn

                ElseIf ("datetime".Equals(objClsVariable.valueColumn.GetType.Name.ToLower)) Then
                    .Parameters.Add(":" + objClsVariable.nameColumn, OleDbType.Date).Value = objClsVariable.valueColumn

                End If

            Next

        End With
    End Sub

    Private Sub AddParameter2(ByVal clnDataColumn As Collection)
        Dim countCollection As Integer = 0
        countCollection = clnDataColumn.Count
        Dim objClsVariable As clsVariable = New clsVariable
        With objCmd

            For Each Data As Object In clnDataColumn
                objClsVariable = Data

                If objClsVariable.valueColumn Is Nothing Then
                    .Parameters.AddWithValue(":" + objClsVariable.nameColumn, DBNull.Value)
                    Continue For
                End If

                If objClsVariable.nameColumn = "sERROR" Then
                    .Parameters.Add(":sERROR", OleDbType.VarChar, 100)
                    .Parameters(":sERROR").Direction = ParameterDirection.Output
                    .CommandType = CommandType.StoredProcedure
                    Continue For
                End If

                If ("string".Equals(objClsVariable.valueColumn.GetType.Name.ToLower)) Then
                    ' .Parameters.Add(":" + objClsVariable.nameColumn, OleDbType.VarWChar).Value = objClsVariable.valueColumn
                    .Parameters.Add(":" + objClsVariable.nameColumn, OleDbType.VarChar).Value = objClsVariable.valueColumn

                ElseIf ("integer".Equals(objClsVariable.valueColumn.GetType.Name.ToLower)) Then
                    .Parameters.Add(":" + objClsVariable.nameColumn, OleDbType.Integer).Value = objClsVariable.valueColumn

                ElseIf ("int32".Equals(objClsVariable.valueColumn.GetType.Name.ToLower)) Then
                    .Parameters.Add(":" + objClsVariable.nameColumn, OleDbType.Integer).Value = objClsVariable.valueColumn

                ElseIf ("double".Equals(objClsVariable.valueColumn.GetType.Name.ToLower)) Then
                    .Parameters.Add(":" + objClsVariable.nameColumn, OleDbType.Double).Value = objClsVariable.valueColumn

                ElseIf ("long".Equals(objClsVariable.valueColumn.GetType.Name.ToLower)) Then
                    .Parameters.Add(":" + objClsVariable.nameColumn, OleDbType.Double).Value = objClsVariable.valueColumn

                ElseIf ("datetime".Equals(objClsVariable.valueColumn.GetType.Name.ToLower)) Then
                    .Parameters.Add(":" + objClsVariable.nameColumn, OleDbType.Date).Value = objClsVariable.valueColumn

                End If

            Next

        End With
    End Sub

    Public Function QueryDataTable(ByVal strSQL As String) As DataTable
        Dim dtAdapter As OleDbDataAdapter
        Dim dt As New DataTable
        objConn = New OleDbConnection
        With objConn
            .ConnectionString = strConnString
            .Open()
        End With
        dtAdapter = New OleDbDataAdapter(strSQL, objConn)
        dtAdapter.Fill(dt)
        Return dt '*** Return DataTable ***'
    End Function

    Public Function QueryDataTableAddParameter(ByVal strSQL As String, ByVal clnDataColumn As Collection) As DataTable
        Dim dt As New DataTable
        Dim dtAdapter As New OleDbDataAdapter
        objConn = New OleDbConnection
        With objConn
            .ConnectionString = strConnString
            .Open()
        End With
        Try
            objCmd = New OleDbCommand
            With objCmd
                .Connection = objConn
                .CommandType = CommandType.Text
                .CommandText = strSQL

            End With
            AddParameter(clnDataColumn)
            dtAdapter.SelectCommand = objCmd
            dtAdapter.Fill(dt)
            objConn.Close()
        Catch ex As Exception
            Throw ex
            dt = Nothing
            '*** Return False ***'
        End Try

        Return dt '*** Return DataTable ***'
    End Function

    Public Function QueryExecuteNonQuery(ByVal strSQL As String) As Boolean
        objConn = New OleDbConnection
        With objConn
            .ConnectionString = strConnString
            .Open()
        End With
        Try
            objCmd = New OleDbCommand
            With objCmd
                .Connection = objConn
                .CommandType = CommandType.Text
                .CommandText = strSQL
            End With

            ' AddParameter(clnDataColumn)
            objCmd.ExecuteNonQuery()
            Return True '*** Return True ***'
        Catch ex As Exception
            Return False '*** Return False ***'
        End Try
    End Function

    Public Function QueryExecuteNonQueryAddParameter(ByVal strSQL As String, ByVal clnDataColumn As Collection) As Boolean
        Dim dt As New DataTable
        Dim dtAdapter As New OleDbDataAdapter
        objConn = New OleDbConnection
        With objConn
            .ConnectionString = strConnString
            .Open()
        End With
        Try
            objCmd = New OleDbCommand
            With objCmd
                .Connection = objConn
                .CommandType = CommandType.Text
                .CommandText = strSQL
            End With

            AddParameter2(clnDataColumn)
            objCmd.ExecuteNonQuery()
            objConn.Close()
            Return True
        Catch ex As Exception
            Return False
            '*** Return False ***'
        End Try
    End Function

    ' for Transaction
    Public Function QueryExecuteNonQueryAddParameter2(ByVal strSQL As String, ByVal clnDataColumn As Collection, Optional ByVal sERROR As String = "") As Boolean
        Dim dt As New DataTable
        Dim dtAdapter As New OleDbDataAdapter
        Try
            objCmd = New OleDbCommand
            With objCmd
                .Connection = objConn
                .CommandType = CommandType.Text
                .CommandText = strSQL
                .Transaction = Trans
            End With

            AddParameter2(clnDataColumn)
            objCmd.ExecuteNonQuery()

            'If objCmd.CommandType = CommandType.StoredProcedure Then
            '    sERROR = objCmd.Parameters("sERROR").Value
            'End If

            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function QueryProcedureExecuteNonQuery(ByVal strSQL As String, ByVal strParameter As String, ByVal strValuePrm As String) As Boolean
        objConn = New OleDbConnection
        With objConn
            .ConnectionString = strConnString
            .Open()
        End With
        Try
            objCmd = New OleDbCommand
            With objCmd
                .Connection = objConn
                .CommandType = CommandType.StoredProcedure
                .CommandText = strSQL
                .Parameters.Add(strParameter, OleDbType.VarWChar).Value = strValuePrm
            End With
            objCmd.ExecuteNonQuery()
            Return True '*** Return True ***'
        Catch ex As Exception
            Return False '*** Return False ***'
        End Try
    End Function

    Public Function QueryExecuteScalar(ByVal strSQL As String) As Object
        Dim obj As Object
        objConn = New OleDbConnection
        With objConn
            .ConnectionString = strConnString
            .Open()
        End With
        Try
            objCmd = New OleDbCommand
            With objCmd
                .Connection = objConn
                .CommandType = CommandType.Text
                .CommandText = strSQL
            End With
            obj = objCmd.ExecuteScalar() '*** Return Scalar ***'
            Return obj
        Catch ex As Exception
            Return Nothing '*** Return Nothing ***'
        End Try
    End Function

    Public Sub TransStart()
        objConn = New OleDbConnection
        With objConn
            .ConnectionString = strConnString
            .Open()
        End With
        Trans = objConn.BeginTransaction(IsolationLevel.ReadCommitted)
    End Sub

    Public Function TransExecute(ByVal strSQL As String, ByVal clnDataColumn As Collection) As Boolean
        objCmd = New OleDbCommand
        With objCmd
            .Connection = objConn
            .Transaction = Trans
            .CommandType = CommandType.Text
            .CommandText = strSQL
        End With
        AddParameter(clnDataColumn)
        Return objCmd.ExecuteNonQuery()
    End Function

    Public Sub TransRollBack()
        Trans.Rollback()
    End Sub

    Public Sub TransCommit()
        Trans.Commit()
    End Sub

    Public Sub Close()
        objConn.Close()
        objConn = Nothing
    End Sub

End Class
